package unilearn.da;

import java.sql.*;
import java.util.Vector;
import javax.naming.*;

import unilearn.domain.Subject;

/**
 * @author Krish Nair, 5418763
 * @author Ben Morrall, 4158768
 * @version 0.1, 30 Apr 2008, initial creation
 * @version 0.2, 14 May 2008, Refactor code, blm
 */
public class DASubject extends DABase
{

	public static void deleteSubject(long subjectID) throws SQLException, NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("DELETE FROM SUBJECT WHERE subject_id = ?");
			ps.setLong(1, subjectID);

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}

	}

	public static Vector<Subject> getAllSubjects() throws SQLException, NamingException
	{
		Vector<Subject> subject = new Vector<Subject>();
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject");

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				subject.add(getSubjectByID(rs.getLong("subjectID")));
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return subject;
	}

	public static Subject getSubjectByCode(String subjectCode) throws SQLException,
		NamingException
	{
		Subject subject = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject where subject_code = ?");
			ps.setString(1, subjectCode);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				subject = new Subject(rs.getLong("subject_id"));

				subject.setSubjectCode(rs.getString("subject_code"));
				subject.setSubjectName(rs.getString("subject_name"));
				subject.setSemester(rs.getInt("semester"));
				subject.setYear(rs.getInt("year_running"));
				subject.setDescription(rs.getString("description"));
				subject.setConvenorName(rs.getString("convenor_name"));
				subject.setConvenorEmail(rs.getString("convenor_email"));
				return subject;
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}
		return null;

	}

	public static Subject getSubjectByID(long subjectID) throws SQLException, NamingException
	{
		Subject subject = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject where subject_id = ?");
			ps.setLong(1, subjectID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				subject = new Subject(rs.getLong("subject_id"));

				subject.setSubjectCode(rs.getString("subject_code"));
				subject.setSubjectName(rs.getString("subject_name"));
				subject.setSemester(rs.getInt("semester"));
				subject.setYear(rs.getInt("year_running"));
				subject.setDescription(rs.getString("description"));
				subject.setConvenorName(rs.getString("convenor_name"));
				subject.setConvenorEmail(rs.getString("convenor_email"));
			}

		}
		finally
		{
			if (ps != null) ps.close();
		}

		return subject;
	}

	public static long insertSubject(Subject subject) throws SQLException, NamingException
	{
		long subjectID = 0;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(
					"INSERT INTO SUBJECT (subject_code,subject_name,semester,year_running,description,convenor_name,convenor_email) "
							+ "VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, subject.getSubjectCode());
			ps.setString(2, subject.getSubjectName());
			ps.setInt(3, subject.getSemester());
			ps.setInt(4, subject.getYear());
			ps.setString(5, subject.getDescription());
			ps.setString(6, subject.getConvenorName());
			ps.setString(7, subject.getConvenorEmail());

			ps.execute();

			ResultSet results = ps.getGeneratedKeys();
			if (results.next())
			{
				subjectID = results.getLong(1);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return subjectID;
	}

	public static void updateSubject(long subjectID, Subject subject) throws SQLException,
		NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE SUBJECT SET subject_code = ?, subject_name = ?, semester = ?, "
					+ "year_running = ?, description = ?, convenor_name = ? , convenor_email = ? WHERE subject_id = ?");

			ps.setString(1, subject.getSubjectCode());
			ps.setString(2, subject.getSubjectName());
			ps.setInt(3, subject.getSemester());
			ps.setInt(4, subject.getYear());
			ps.setString(5, subject.getDescription());
			ps.setString(6, subject.getConvenorName());
			ps.setString(7, subject.getConvenorEmail());
			ps.setLong(8, subjectID);

			ps.execute();

		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	/**
	 * Prevent Instances of DASubject
	 */
	private DASubject()
	{
	}

}
